package cn.edu.swu.picture;

import cn.edu.swu.core.AbstractRepo;
import cn.edu.swu.core.ResultSetVisitor;

import java.awt.print.Book;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

//完成所有数据库的增删改查，即所有的数据库访问
public class PictureRepo extends AbstractRepo {

    private SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-mm-dd");

    //查询并取出
    public List<Picture> getAllPictures () throws SQLException, ClassNotFoundException {
        List<Picture> pictures = new ArrayList<>();
        String sql = "SELECT * FROM picture";
        this.query(sql, new ResultSetVisitor() {
            @Override
            public void visit(ResultSet rs) throws SQLException{
                Picture pi = new Picture();
                pi.setId(rs.getInt("id"));
                pi.setType(rs.getString("Type"));
                pi.setDOL(rs.getString("DOL"));
                pi.setMemo(rs.getString("memo"));
                pi.setStore(rs.getDate("store"));
                pi.setImage(rs.getString("image"));
                pictures.add(pi);
            }
        });


        //建立数据库的连接
//        try (Connection connection = getConnection()) {  //static连接数据库后就不用this了
//            //创建一个访问数据库的陈述
//            try (Statement statement = connection.createStatement()) {
//                //查询
//                String sql = "SELECT * FROM picture";
//                ResultSet rs = statement.executeQuery(sql);
//                //用while循环逐条取出每条数据
//                while (rs.next()) {
//                    Picture pi = new Picture();
//                    pi.setId(rs.getInt("id"));
//                    pi.setType(rs.getString("Type"));
//                    pi.setDOL(rs.getString("DOL"));
//                    pi.setMemo(rs.getString("memo"));
//                    pi.setStore(rs.getDate("store"));
//
//                    //每个picture都依次添加进去
//                    pictures.add(pi);
//                }
//            }
//        }

        return pictures;
    }

    public void deleteById(int id) throws SQLException {
        String template = "DELETE FROM picture WHERE id = %s " ;
        this.execute(String.format(template,id));
    }

    public void save(Picture picture) throws SQLException {
        String template = "INSERT INTO picture (type,DOL,memo,store,image) " + "value ('%s', '%s', '%s', '%s', '%s')";
        this.execute(String.format(template,picture.getType(),picture.getDOL(),picture.getMemo(),simpleDateFormat.format(picture.getStore()), picture.getImage()));
    }

    public Picture getById(int id) throws SQLException {
        List<Picture> pictures = new ArrayList<>();
        String sql = "SELECT * FROM picture WHERE id=" + id;
        this.query(sql, new ResultSetVisitor(){
            @Override
            public void visit(ResultSet rs) throws SQLException {

                pictures.add(getOneRecord(rs));
            }
        });
        return pictures.size() > 0 ? pictures.get(0) : null;
    }

    public List<Picture> search(String key) throws SQLException {
        List<Picture> pictures = new ArrayList<>();
        String sql = "SELECT * FROM picture WHERE type LIKE '%" + key + "' OR memo Like '%" + key + "%'";
        this.query(sql, new ResultSetVisitor(){
            @Override
            public void visit(ResultSet rs) throws SQLException {
                pictures.add(getOneRecord(rs));
            }
        });
        return pictures;
    }

    private Picture getOneRecord(ResultSet rs) throws SQLException {
        Picture pi = new Picture();
        pi.setId(rs.getInt("id"));
        pi.setType(rs.getString("Type"));
        pi.setDOL(rs.getString("DOL"));
        pi.setMemo(rs.getString("memo"));
        pi.setStore(rs.getDate("store"));
        pi.setImage(rs.getString("image"));
        return pi;
    }

    public void update(Picture picture) throws SQLException {
        String template = "UPDATE picture SET type ='%s', DOL='%s', memo='%s', store='%s', image='%s' WHERE id='%s'";
        this.execute(String.format(template,picture.getType(),picture.getDOL(),picture.getMemo(),simpleDateFormat.format(picture.getStore()), picture.getImage(),picture.getId()));
    }
}
